from io import BytesIO

from sqlalchemy.orm import Session, joinedload

from yg_crm.crud import get_checkBill_by_billNos
import pandas as pd
from openpyxl import load_workbook
from utils.log_util  import logger as log
from yg_crm.orm import CheckingBill, CheckingBillFee


def getBillDetailByBillNo(db: Session, billNos: list):
     res = get_checkBill_by_billNos(db, bill_nos=billNos)
     # print(res)
     return res



async def getExcelFileCreateYear(file_path:BytesIO):
     # 读取Excel文件
     df = pd.read_excel(file_path, dtype=str)

     # 使用openpyxl加载工作簿
     workbook = load_workbook(file_path)
     properties = workbook.properties

     # 获取文件的创建日期
     creation_date = properties.created
     creation_date_str = creation_date.strftime('%Y-%m-%d')
     return creation_date_str[:4]


# 批量创建或更新对象
def add_or_update_records(session, ormObjects):
    for o in ormObjects:
        session.merge(o)
    session.commit()

def search_check_bill(db: Session, params:dict):
    log.info("param:{}", params)
    al = db.query(CheckingBill).options(joinedload(CheckingBill.checkingBillFees).joinedload(CheckingBillFee.yGFeeType, innerjoin=True))
    if params.get("bill_no"):
        al = al.filter(CheckingBill.bill_no == params.get("bill_no"))
    if params.get("bill_date"):
        al = al.filter(CheckingBill.bill_date.between(*params.get("bill_date")))
    if params.get("id_trade_partner"):
        al = al.filter(CheckingBill.id_trade_partner == params.get("id_trade_partner"))
    if params.get("id_company"):
        al = al.filter(CheckingBill.id_company == str(params.get("id_company")))
    if params.get("status"):
        al = al.filter(CheckingBill.status.in_(params.get("status")))
    if params.get("destination_port"):
        al = al.filter(CheckingBill.destination_port == params.get("destination_port"))
    if params.get("ids"):
        al = al.filter(CheckingBill.id.in_(params.get("ids")))
    return al



